﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
//using Oracle.DataAccess.Client;
using System.Configuration;
//using System.Data.Entity.Infrastructure;
using System.ComponentModel.DataAnnotations;
using trangbaocao.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;
namespace trangbaocao.Models
{
          
    public class UserModel
    {
        public int id { get; set; }
        [Required(ErrorMessage = "Tên đăng nhập không được để trống!")]
        public string user_name { get; set; }
        [Required(ErrorMessage = "Mật khẩu không được để trống!")]
        public string password { get; set; }        
        public int stations_id { get; set; }
        public SelectList tram_id { get; set; }
        public int main_stations_id { get; set; }
        public SelectList trungtam_id { get; set; }
        public int quyen { get; set; }
        public SelectList permission_id { get; set; }
        public int status { get; set; }
        public string ho_ten { get; set; }
        public string so_dt { get; set; }
        public string email { get; set; }
        public string ghi_chu { get; set; }
        public bool RememberMe { get; set; }
        
        public  List<UserModel> getUser()
        {
            List<UserModel> user_ = new List<UserModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT * FROM user_dn");
            while (odr.Read())
                user_.Add(new UserModel {id=Convert.ToInt32(odr[0].ToString()), user_name = odr[1].ToString(), password = odr[2].ToString(), stations_id = Convert.ToInt32(odr[3].ToString()), main_stations_id = Convert.ToInt32(odr[4].ToString()), quyen = Convert.ToInt32(odr[5].ToString()), status = Convert.ToInt32(odr[6].ToString()), ho_ten = odr[7].ToString(), so_dt = odr[8].ToString(), email = odr[9].ToString(), ghi_chu = odr[10].ToString() });
            return user_;
        }
        public List<UserModel> getUserByName(string name)
        {
            List<UserModel> user_ = new List<UserModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT * FROM user_dn where user_name='"+name+"'");
            while (odr.Read())
                user_.Add(new UserModel { id = Convert.ToInt32(odr[0].ToString()), user_name = odr[1].ToString(), password = odr[2].ToString(), stations_id = Convert.ToInt32(odr[3].ToString()), main_stations_id = Convert.ToInt32(odr[4].ToString()), quyen = Convert.ToInt32(odr[5].ToString()), status = Convert.ToInt32(odr[6].ToString()), ho_ten = odr[7].ToString(), so_dt = odr[8].ToString(), email = odr[9].ToString(), ghi_chu = odr[10].ToString() });
            return user_;
        }
        public static int IsValid(string username, string password)
        {
            string sql = "SELECT * FROM user_dn WHERE user_name='" + username + "' and password='" + password + "' and status=1";            
            return General.Lib_CheckExist(sql);
        }
        public void addUser(UserModel us)
        {
            string sql = "INSERT INTO user_dn";
            sql += " select nvl((select max(id)+1 from user_dn),1),'" + us.user_name + "','" + us.password + "'," + us.stations_id + "," + us.main_stations_id + "," + us.quyen + "," + us.status + ",'" + us.ho_ten + "','" + us.so_dt + "','" + us.email + "','" + us.ghi_chu + "' from dual";
            General.Lib_ExecuteNonQuery(sql);
        }

        public void updateUser(UserModel us)
        {
            string sql = "UPDATE user_dn SET user_name='" + us.user_name + "', password='" + us.password + "', stations_id=" + us.stations_id + ", main_stations_id=" + us.main_stations_id + ", quyen=" + us.quyen + ", status=" + us.status + ", ho_ten='" + us.ho_ten + "', so_dt='" + us.so_dt + "', email='" + us.email + "', ghi_chu='" + us.ghi_chu + "' where id='" + us.id + "'";
            General.Lib_ExecuteNonQuery(sql);
        }
        public void updateCurrentUser(UserModel us)
        {
            string sql = "UPDATE user_dn SET user_name='" + us.user_name + "', password='" + us.password + "', status=" + us.status + ", ho_ten='" + us.ho_ten + "', so_dt='" + us.so_dt + "', email='" + us.email + "', ghi_chu='" + us.ghi_chu + "' where id='" + us.id + "'";
            General.Lib_ExecuteNonQuery(sql);
        }
        public void deleteUser(int id)
        {
            string sql = "DELETE FROM user_dn WHERE id=" + id + "";
            General.Lib_ExecuteNonQuery(sql);
        }
        public int checkExist(UserModel us)
        {
            string sql = "SELECT * FROM user_dn WHERE user_name='" + us.user_name + "'";
            return General.Lib_CheckExist(sql);
        }

    }
    public class UserAssociateModel
    {
        public int id { get; set; }
        public string user_name { get; set; }
        public string password { get; set; }
        public string stations_name { get; set; }
        public string main_stations_name { get; set; }
        public string quyen { get; set; }
        public string status { get; set; }
        public string ho_ten { get; set; }
        public string so_dt { get; set; }
        public string email { get; set; }
        public string ghi_chu { get; set; }
        public bool RememberMe { get; set; }

        public List<UserAssociateModel> getUser()
        {
            List<UserAssociateModel> user_ = new List<UserAssociateModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT a.id,a.user_name,a.password,b.name,c.name,d.name,a.status,a.ho_ten,a.so_dt,a.email,a.ghi_chu FROM user_dn a,stations b,main_stations c,permission d where a.stations_id=b.id and a.main_stations_id=c.id and a.quyen=d.id order by a.id");
            while (odr.Read())
                user_.Add(new UserAssociateModel { id = Convert.ToInt32(odr[0].ToString()), user_name = odr[1].ToString(), password = odr[2].ToString(), stations_name = odr[3].ToString(), main_stations_name = odr[4].ToString(), quyen = odr[5].ToString(), status = Convert.ToInt32(odr[6].ToString())==1?"Bình thường":"Khóa", ho_ten = odr[7].ToString(), so_dt = odr[8].ToString(), email = odr[9].ToString(), ghi_chu = odr[10].ToString() });
            return user_;
        }
        public List<UserAssociateModel> getUserByName(string name)
        {
            List<UserAssociateModel> user_ = new List<UserAssociateModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT a.id,a.user_name,a.password,b.name,c.name,d.name,a.status,a.ho_ten,a.so_dt,a.email,a.ghi_chu FROM user_dn a,stations b,main_stations c,permission d where a.stations_id=b.id and a.main_stations_id=c.id and a.quyen=d.id and a.user_name='"+name+"' order by a.id");
            while (odr.Read())
                user_.Add(new UserAssociateModel { id = Convert.ToInt32(odr[0].ToString()), user_name = odr[1].ToString(), password = odr[2].ToString(), stations_name = odr[3].ToString(), main_stations_name = odr[4].ToString(), quyen = odr[5].ToString(), status = Convert.ToInt32(odr[6].ToString()) == 1 ? "Bình thường" : "Khóa", ho_ten = odr[7].ToString(), so_dt = odr[8].ToString(), email = odr[9].ToString(), ghi_chu = odr[10].ToString() });
            return user_;
        }
    }
    
}
